package com.backend.modules.task.repository;

import com.backend.modules.system.entity.SysUser;
import com.backend.modules.task.entity.TaskSubmission;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.time.LocalDateTime;
import com.backend.modules.statistics.vo.AdminStatisticsVO;
import com.backend.modules.statistics.vo.TeacherStatisticsVO;
import com.backend.modules.statistics.vo.StudentStatisticsVO;

/**
 * 任务提交记录数据访问接口
 */
@Repository
public interface TaskSubmissionRepository extends JpaRepository<TaskSubmission, Long>, 
        JpaSpecificationExecutor<TaskSubmission> {
    
    /**
     * 统计任务的总提交次数
     * @param taskId 任务ID
     * @return 提交次数
     */
    @Query("SELECT COUNT(ts) FROM TaskSubmission ts WHERE ts.taskId = ?1")
    int countByTaskId(Long taskId);
    
    /**
     * 统计任务的已提交学生数量
     * @param taskId 任务ID
     * @return 已提交学生数量
     */
    @Query("SELECT COUNT(DISTINCT ts.student.id) FROM TaskSubmission ts WHERE ts.taskId = ?1")
    int countSubmittedStudentsByTaskId(Long taskId);
    
    /**
     * 查询学生的任务提交历史
     */
    List<TaskSubmission> findByTaskIdAndStudentIdOrderBySubmitTimeDesc(Long taskId, Long studentId);
    
    /**
     * 查询学生是否已提交任务
     */
    boolean existsByTaskIdAndStudentId(Long taskId, Long studentId);
    
    /**
     * 按提交时间倒序查询任务的所有提交记录
     */
    List<TaskSubmission> findByTaskIdOrderBySubmitTimeDesc(Long taskId);
    
    /**
     * 查询最新的提交记录
     */
    Optional<TaskSubmission> findFirstByTaskIdAndStudentIdOrderBySubmitTimeDesc(Long taskId, Long studentId);

    @Query("SELECT ts FROM TaskSubmission ts " +
           "LEFT JOIN FETCH ts.task " +
           "LEFT JOIN FETCH ts.student " +
           "LEFT JOIN FETCH ts.files " +
           "WHERE ts.id = :id")
    Optional<TaskSubmission> findByIdWithDetails(@Param("id") Long id);

    @Query("SELECT DISTINCT s.student FROM TaskSubmission s " +
        "WHERE (:gradeId IS NULL OR s.task.classInfo.parent.parent.id = :gradeId) " +
        "AND (:majorId IS NULL OR s.task.classInfo.parent.id = :majorId) " +
        "AND (:classId IS NULL OR s.task.classInfo.id = :classId) " +
        "AND s.status = 'PENDING'")
    List<SysUser> findUnsubmittedStudents(
        @Param("gradeId") Long gradeId,
        @Param("majorId") Long majorId,
        @Param("classId") Long classId);

    @Query("SELECT s FROM TaskSubmission s " +
        "WHERE (:gradeId IS NULL OR s.task.classInfo.parent.parent.id = :gradeId) " +
        "AND (:majorId IS NULL OR s.task.classInfo.parent.id = :majorId) " +
        "AND (:classId IS NULL OR s.task.classInfo.id = :classId)")
    List<TaskSubmission> findByConditions(
        @Param("gradeId") Long gradeId,
        @Param("majorId") Long majorId,
        @Param("classId") Long classId);

    @Query("SELECT COUNT(s) FROM TaskSubmission s WHERE s.deleted = false")
    Integer countAll();
    
    @Query("SELECT COUNT(s) FROM TaskSubmission s WHERE s.deleted = false " +
           "AND DATE(s.submitTime) = DATE(:date)")
    Integer countBySubmitDate(@Param("date") LocalDateTime date);
    
    @Query("SELECT COUNT(s) FROM TaskSubmission s WHERE s.deleted = false " +
           "AND s.submitTime > s.task.deadline " +
           "AND DATE(s.submitTime) = DATE(:date)")
    Integer countOverdueBySubmitDate(@Param("date") LocalDateTime date);
    
    @Query("SELECT AVG(CASE WHEN s.status = 'APPROVED' THEN 1.0 ELSE 0.0 END) " +
           "FROM TaskSubmission s WHERE s.deleted = false")
    Double calculateAverageSubmitRate();

    @Query("SELECT new com.backend.modules.statistics.vo.AdminStatisticsVO$DepartmentSubmitRate(" +
        "d.id, " +
        "d.name, " +
        "CAST(COUNT(CASE WHEN s.status = 'APPROVED' THEN 1 ELSE NULL END) AS double) / CAST(COUNT(*) AS double), " +
        "COUNT(*), " +
        "COUNT(CASE WHEN s.status = 'APPROVED' THEN 1 ELSE NULL END)) " +
        "FROM TaskSubmission s " +
        "JOIN s.task t " +
        "JOIN t.classInfo c " +
        "JOIN c.parent m " +
        "JOIN m.parent d " +
        "WHERE s.deleted = false " +
        "AND (:gradeId IS NULL OR d.id = :gradeId) " +
        "AND (:majorId IS NULL OR m.id = :majorId) " +
        "AND (:classId IS NULL OR c.id = :classId) " +
        "GROUP BY d.id, d.name " +
        "ORDER BY COUNT(CASE WHEN s.status = 'APPROVED' THEN 1 ELSE NULL END) * 1.0 / COUNT(*) DESC")
    List<AdminStatisticsVO.DepartmentSubmitRate> calculateDepartmentSubmitRates(
        @Param("gradeId") Long gradeId,
        @Param("majorId") Long majorId,
        @Param("classId") Long classId
    );

    @Query("SELECT new com.backend.modules.statistics.vo.TeacherStatisticsVO$ClassSubmitStatus(" +
        "d.id, " +
        "d.name, " +
        "(SELECT COUNT(u) FROM SysUser u WHERE u.department = d AND u.userType = 3), " +
        "COUNT(DISTINCT s.student), " +
        "(SELECT COUNT(u) FROM SysUser u WHERE u.department = d AND u.userType = 3) - COUNT(DISTINCT s.student), " +
        "CAST(COUNT(DISTINCT s.student) AS double) / CAST((SELECT COUNT(u) FROM SysUser u WHERE u.department = d AND u.userType = 3) AS double)) " +
        "FROM TaskSubmission s " +
        "RIGHT JOIN s.task t " +
        "RIGHT JOIN t.classInfo d " +
        "WHERE t.creatorId = :teacherId " +
        "AND t.deleted = false " +
        "AND (:classId IS NULL OR d.id = :classId) " +
        "GROUP BY d.id, d.name")
    List<TeacherStatisticsVO.ClassSubmitStatus> calculateClassSubmitStatus(
        @Param("teacherId") Long teacherId,
        @Param("classId") Long classId
    );

    @Query("SELECT COUNT(t) FROM Task t " +
        "WHERE t.creatorId = :teacherId " +
        "AND t.deleted = false")
    Integer countTeacherTasks(@Param("teacherId") Long teacherId);

    @Query("SELECT COUNT(t) FROM Task t " +
        "WHERE t.creatorId = :teacherId " +
        "AND t.deleted = false " +
        "AND t.deadline > CURRENT_TIMESTAMP")
    Integer countTeacherOngoingTasks(@Param("teacherId") Long teacherId);

    @Query("SELECT COUNT(s) FROM TaskSubmission s " +
        "WHERE s.task.creatorId = :teacherId " +
        "AND s.deleted = false")
    Integer countTeacherSubmissions(@Param("teacherId") Long teacherId);

    @Query("SELECT COUNT(s) FROM TaskSubmission s " +
        "WHERE s.task.creatorId = :teacherId " +
        "AND s.status = 'PENDING' " +
        "AND s.deleted = false")
    Integer countTeacherPendingReviews(@Param("teacherId") Long teacherId);

    @Query("SELECT AVG(CASE WHEN s.status = 'APPROVED' THEN 1.0 ELSE 0.0 END) " +
        "FROM TaskSubmission s " +
        "WHERE s.task.creatorId = :teacherId " +
        "AND s.deleted = false")
    Double calculateTeacherAverageSubmitRate(@Param("teacherId") Long teacherId);

    @Query("SELECT COUNT(s) FROM TaskSubmission s " +
           "WHERE s.deleted = false " +
           "AND s.task.creatorId = :teacherId " +
           "AND DATE(s.submitTime) = DATE(:date)")
    Integer countBySubmitDateAndTeacher(
        @Param("date") LocalDateTime date,
        @Param("teacherId") Long teacherId
    );

    @Query("SELECT COUNT(s) FROM TaskSubmission s " +
           "WHERE s.deleted = false " +
           "AND s.task.creatorId = :teacherId " +
           "AND s.submitTime > s.task.deadline " +
           "AND DATE(s.submitTime) = DATE(:date)")
    Integer countOverdueBySubmitDateAndTeacher(
        @Param("date") LocalDateTime date,
        @Param("teacherId") Long teacherId
    );

    // 统计学生的进行中任务数
    @Query("SELECT COUNT(t) FROM Task t " +
           "WHERE t.classInfo.id = (SELECT u.department.id FROM SysUser u WHERE u.id = :studentId) " +
           "AND t.deadline > CURRENT_TIMESTAMP " +
           "AND t.deleted = false")
    Integer countStudentOngoingTasks(@Param("studentId") Long studentId);

    // 统计学生的已完成任务数
    @Query("SELECT COUNT(DISTINCT s.task) FROM TaskSubmission s " +
           "WHERE s.student.id = :studentId " +
           "AND s.status = 'APPROVED' " +
           "AND s.deleted = false")
    Integer countStudentCompletedTasks(@Param("studentId") Long studentId);

    // 统计学生的逾期任务数
    @Query("""
           SELECT COUNT(t) FROM Task t
           WHERE t.classInfo.id = (SELECT u.department.id FROM SysUser u WHERE u.id = :studentId)
           AND t.deadline < CURRENT_TIMESTAMP
           AND t.deleted = false
           """)
    Integer countStudentOverdueTasks(@Param("studentId") Long studentId);

    // 计算学生的提交率
    @Query("SELECT CAST(COUNT(DISTINCT CASE WHEN s.status = 'APPROVED' THEN s.task.id END) AS double) / " +
           "CAST((SELECT COUNT(t) FROM Task t WHERE t.classInfo = (SELECT u.department FROM SysUser u WHERE u.id = :studentId)) AS double) " +
           "FROM TaskSubmission s " +
           "WHERE s.student.id = :studentId " +
           "AND s.deleted = false")
    Double calculateStudentSubmitRate(@Param("studentId") Long studentId);

    // 获取学生的提交历史
    @Query("SELECT new com.backend.modules.statistics.vo.StudentStatisticsVO$SubmissionHistory(" +
           "t.name, " +
           "s.submitTime, " +
           "s.status, " +
           "s.reviewComment) " +
           "FROM TaskSubmission s " +
           "JOIN s.task t " +
           "WHERE s.student.id = :studentId " +
           "AND s.deleted = false " +
           "ORDER BY s.submitTime DESC")
    List<StudentStatisticsVO.SubmissionHistory> getStudentSubmissionHistory(@Param("studentId") Long studentId);



    // 获取班级总人数
    @Query("SELECT COUNT(u) FROM SysUser u " +
           "WHERE u.department = (SELECT s.department FROM SysUser s WHERE s.id = :studentId) " +
           "AND u.userType = 3")
    Integer getClassTotalStudents(@Param("studentId") Long studentId);

    // 获取当前排名
    @Query("SELECT COUNT(DISTINCT t.student.id) + 1 " +
           "FROM TaskSubmission t " +
           "WHERE t.student.department = (SELECT s.department FROM SysUser s WHERE s.id = :studentId) " +
           "AND t.deleted = false " +
           "GROUP BY t.student.id " +
           "HAVING (SUM(CASE WHEN t.status = 'APPROVED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)) > " +
           "(SELECT COALESCE(SUM(CASE WHEN s2.status = 'APPROVED' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 0.0) " +
           " FROM TaskSubmission s2 " +
           " WHERE s2.student.id = :studentId " +
           " AND s2.deleted = false)")
    Integer getCurrentRank(@Param("studentId") Long studentId);

    // 获取学生提交率
    @Query("SELECT COALESCE(SUM(CASE WHEN s.status = 'APPROVED' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0), 0.0) " +
           "FROM TaskSubmission s " +
           "WHERE s.student.id = :studentId " +
           "AND s.deleted = false")
    Double getStudentSubmitRate(@Param("studentId") Long studentId);

    // 获取班级平均提交率
    @Query("SELECT COALESCE(SUM(CASE WHEN s.status = 'APPROVED' THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(*), 0), 0.0) " +
           "FROM TaskSubmission s " +
           "WHERE s.student.department = (SELECT s2.department FROM SysUser s2 WHERE s2.id = :studentId) " +
           "AND s.deleted = false " +
           "GROUP BY s.student.id")
    List<Double> getClassAverageSubmitRate(@Param("studentId") Long studentId);

    /**
     * 根据任务ID查找所有提交记录
     * @param taskId 任务ID
     * @return 提交记录列表
     */
    List<TaskSubmission> findByTaskId(Long taskId);
}